import os
import pandas as pd
import tkinter as tk
from tkinter import filedialog

def main():
    root = tk.Tk()
    root.withdraw()  # 隐藏主窗口
    excel_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
    if not excel_path:
        print("未选择文件")
        return

    source_data_sheet_name = "源数据"
    project_time_archive_sheet_name = "项目工时归档"
    process_1_sheet_name = "处理1"
    dept_all_contracts_hours_sheet_name = "按部门对所有合同号进行工时统计"

    # 关闭屏幕更新和警告
    pd.options.display.notebook_repr_html = False

    # 删除源数据表内容
    with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
        book = writer.book
        if source_data_sheet_name in book.sheetnames:
            sheet = book[source_data_sheet_name]
            sheet.delete_rows(1, sheet.max_row)

    mypath = os.path.dirname(excel_path)
    for myname in os.listdir(mypath):
        if myname.endswith(".xlsx"):
            wb = pd.read_excel(os.path.join(mypath, myname), sheet_name=project_time_archive_sheet_name)
            lastrow = len(wb)
            lastcol = len(wb.columns)

            # 复制项目工时归档的数据到源数据表
            df_source = wb.copy()
            with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
                df_source.to_excel(writer, sheet_name=source_data_sheet_name, index=False)

            # 删除用工工时为0的行
            df_source = df_source[df_source.iloc[:, 4] != 0]
            with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
                df_source.to_excel(writer, sheet_name=source_data_sheet_name, index=False)

            # 检查HW设计或SW设计的公共合同号是否填写有误
            QJbool = any(
                df_source.apply(lambda row: (row[5] in ["HW设计", "SW设计"]) and str(row[1]).startswith("BA LF"),
                                axis=1))
            if QJbool:
                print("源数据中存在HW设计或SW设计的公共合同号填写有误，不应该填写BA LFXXX，请重新核对后再运行程序")
                return

            break
    else:
        print("宏文件夹内不存在目标文件")
        return

    if not QJbool:
        # copy_from_resource(excel_path, source_data_sheet_name, project_time_archive_sheet_name)
        get_nature(excel_path, source_data_sheet_name, process_1_sheet_name)
        statistics(excel_path, process_1_sheet_name, source_data_sheet_name)
        order_change(excel_path, process_1_sheet_name, dept_all_contracts_hours_sheet_name)
        dept_total(excel_path)  # 假设dept_total函数已定义
        # contract_type(excel_path)  # 假设contract_type函数已定义
        # namechange(excel_path)  # 假设namechange函数已定义
        # addons1(excel_path)  # 假设addons1函数已定义
        # autofit(excel_path)  # 假设autofit函数已定义
        # hide(excel_path)  # 假设hide函数已定义

    print("生成完毕！")


# def copy_from_resource(excel_path, source_data_sheet_name, project_time_archive_sheet_name):
#
#
# # 这部分逻辑已经在main函数中实现了，可以根据需要扩展或修改

def get_nature(excel_path, source_data_sheet_name, process_1_sheet_name):
    df_source = pd.read_excel(excel_path, sheet_name=source_data_sheet_name)
    df_process1 = df_source[['项目编号']].drop_duplicates().reset_index(drop=True)
    df_process1 = pd.concat([df_process1, df_source[['性质']].drop_duplicates().reset_index(drop=True)], axis=1)

    with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
        df_process1.to_excel(writer, sheet_name=process_1_sheet_name, index=False)


def statistics(excel_path, process_1_sheet_name, source_data_sheet_name):
    df_process1 = pd.read_excel(excel_path, sheet_name=process_1_sheet_name)
    df_source = pd.read_excel(excel_path, sheet_name=source_data_sheet_name)

    data = pd.DataFrame(0, index=df_process1.index, columns=df_process1.columns[1:])

    for i, row in df_process1.iterrows():
        project_code = row['项目编号']
        nature = row[1:]

        mask = (df_source['性质'] == nature) & (df_source['项目编号'] == project_code)
        matching_rows = df_source[mask]

        if not matching_rows.empty:
            total_hours = matching_rows['工时'].sum()
            data.iloc[i, 1:] = total_hours

    with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
        df_process1.update(data)
        df_process1.to_excel(writer, sheet_name=process_1_sheet_name, index=False)


def order_change(excel_path, process_1_sheet_name, dept_all_contracts_hours_sheet_name):
    df_process1 = pd.read_excel(excel_path, sheet_name=process_1_sheet_name)
    df_dept_all_contracts_hours = df_process1[df_process1['项目编号'].str.startswith('BA')]

    with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
        df_dept_all_contracts_hours.to_excel(writer, sheet_name=dept_all_contracts_hours_sheet_name, index=False)


# 其他函数（如dept_total, contract_type, namechange, addons1, autofit, hide）应按照类似的模式实现


def dept_total(excel_path):
    # 读取“按部门对所有合同号进行工时统计”工作表的数据
    df_dept_all_contracts_hours = pd.read_excel(excel_path, sheet_name="按部门对所有合同号进行工时统计")

    # 创建一个新DataFrame用于存储“按指定部门顺序统计”的数据
    columns = ["项目编号", "BA技术本部", "制造部", "HW设计", "SW设计", "工程", "工程部", "工程部上海",
               "工程部苏州", "工程部无锡", "工程部北京", "工程部大连", "工程部天津", "工程部深圳",
               "制造", "盘检查", "盘制造", "制造科", "仓库管理", "LF阀门生产", "总计", "BA品质管理"]
    df_dept_order = pd.DataFrame(columns=columns)

    # 复制项目编号列
    df_dept_order["项目编号"] = df_dept_all_contracts_hours.iloc[:, 0]

    # 添加固定的部门名称列
    for col in columns[1:]:
        df_dept_order[col] = 0

    # 获取源数据中的列名
    lastcol = len(df_dept_all_contracts_hours.columns)

    # 根据部门名称匹配并填充数据
    for i in range(1, len(columns)):
        target_col = columns[i]
        for j in range(1, lastcol):
            if df_dept_all_contracts_hours.columns[j] == target_col:
                df_dept_order[target_col] = df_dept_all_contracts_hours.iloc[:, j]
                break

    # 计算合计列
    df_dept_order['制造'] = df_dept_order[['盘检查', '盘制造', '制造科', '仓库管理']].sum(axis=1)
    df_dept_order['工程'] = df_dept_order[
        ['工程部', '工程部上海', '工程部苏州', '工程部无锡', '工程部北京', '工程部大连', '工程部天津',
         '工程部深圳']].sum(axis=1)
    df_dept_order['总计'] = df_dept_order[['HW设计', 'SW设计', '工程', '制造', 'LF阀门生产']].sum(axis=1)

    # 清理零值
    df_dept_order.loc[df_dept_order['制造'] == 0, '制造'] = ''
    df_dept_order.loc[df_dept_order['工程'] == 0, '工程'] = ''
    df_dept_order.loc[df_dept_order['总计'] == 0, '总计'] = ''

    # 特殊情况处理
    num_n = pd.read_excel(excel_path, sheet_name="处理1")['AA1'].iloc[0]
    for i in range(2, num_n + 1):
        project_code = df_dept_order.loc[i - 1, "项目编号"]
        if project_code == "BA制造":
            df_dept_order.loc[i - 1, "制造"] += (
                        df_dept_order.loc[i - 1, "BA技术本部"] + df_dept_order.loc[i - 1, "制造部"])
        elif project_code == "BA技术部设计HW":
            df_dept_order.loc[i - 1, "HW设计"] += (
                        df_dept_order.loc[i - 1, "BA技术本部"] + df_dept_order.loc[i - 1, "制造部"])
        elif project_code == "BA工程":
            df_dept_order.loc[i - 1, "工程"] += df_dept_order.loc[i - 1, "BA技术本部"]
        elif project_code == "BA LF阀门生产":
            df_dept_order.loc[i - 1, "LF阀门生产"] += (
                        df_dept_order.loc[i - 1, "BA技术本部"] + df_dept_order.loc[i - 1, "制造部"])
        elif project_code == "BA技术部设计SW":
            df_dept_order.loc[i - 1, "SW设计"] += df_dept_order.loc[i - 1, "BA技术本部"]

    # 将结果写回到Excel文件中
    with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
        df_dept_order.to_excel(writer, sheet_name="按指定部门顺序统计", index=False)

    input_file_path = excel_path
    df_source = pd.read_excel(input_file_path, sheet_name='按指定部门顺序统计')
    df_target = pd.DataFrame()







    # 删除目标工作表中的所有数据（模拟VBA中的Cells.Delete）
    df_target.drop(df_target.index, inplace=True)

    # 过滤和选择数据
    mask_ba = df_source.iloc[:, 0].str.startswith('BA', na=False)
    df_temp = df_source[mask_ba]

    # 复制并粘贴数据到目标工作表
    df_target = df_temp.copy()
    df_target.reset_index(drop=True, inplace=True)

    # 删除包含'PD'或'LF'的数据行
    mask_delete = df_target.iloc[:, 0].str.contains('PD|LF', na=False)
    df_target = df_target[~mask_delete]

    # 截取字符串前6个字符
    df_target.iloc[:, 0] = df_target.iloc[:, 0].str[:6]

    # 去重
    df_target.drop_duplicates(subset=[df_target.columns[0]], inplace=True)

    # LF数据计算逻辑（此部分需根据实际需求调整）
    areas = ["SH-LF-", "BJ-LF-", "GZ-LF-"]
    sum_columns = [2, 3, 7, 8, 9, 10, 16, 17, 18, 19, 20]
    bool_vars = [False, False, False]

    # 根据您的描述，这部分需要更详细的业务逻辑来准确实现
    # ...

    # 计算F列和O列的总和
    df_target['F'] = df_target.iloc[:, 6:14].sum(axis=1)
    df_target['O'] = df_target.iloc[:, 15:19].sum(axis=1)

    # 根据日期判断年份
    if pd.to_datetime(df_source.iloc[0, 0]).month in [1, 2, 3]:
        m_year = pd.to_datetime(df_source.iloc[0, 0]).year - 1
    else:
        m_year = pd.to_datetime(df_source.iloc[0, 0]).year

    # 在A列后添加年份信息
    df_target.iloc[:, 0] = df_target.iloc[:, 0].astype(str) + str(m_year) + "001"

    # 将处理后的数据写回到新的Excel工作表
    with pd.ExcelWriter(input_file_path, mode='a', engine='openpyxl') as writer:
        df_target.to_excel(writer, sheet_name='工时统计(财务费GP用)', index=False)

    print("处理完成")
    # 示例调用
    # dept_total('your_excel_file.xlsx')



import pandas as pd
from openpyxl import load_workbook

input_file_path = excel_path
# 读取数据
df_source = pd.read_excel(input_file_path, sheet_name='按指定部门顺序统计')
df_target = pd.read_excel(input_file_path, sheet_name='工时统计(财务费GP用)')

# 计算num3
num3 = df_target.shape[0]

# BA公共合同号数据计算
lastcol = df_source.shape[1]
BAsum = [0] * (lastcol - 1)
BAsum1 = [0] * (lastcol - 1)
BAsum2 = [[0 for _ in range(lastcol - 1)] for _ in range(num3)]

for i in range(1, lastcol):
    for j in range(1, len(df_source)):
        if not df_source.iloc[j, 0].startswith('BA LF'):
            BAsum[i - 1] += df_source.iat[j, i]

    for j in range(len(df_source) - num3 + 1, len(df_source)):
        if not (df_source.iloc[j, 0][-2:] == "LF" or df_source.iloc[j, 0][-2:] == "PD"):
            BAsum1[i - 1] += df_source.iat[j, i]

for p in range(1, lastcol):
    for i in range(1, num3 + 1):
        for j in range(len(df_source) - num3 + 1, len(df_source)):
            if df_source.iloc[j, 0][:5] == df_target.iloc[i - 1, 0][:5]:
                BAsum2[i - 1][p - 1] += df_source.iat[j, p]

# 更新目标数据表
for i in range(1, lastcol):
    for j in range(1, num3 + 1):
        if BAsum1[i - 1] != 0:
            df_target.iat[j - 1, i] = BAsum2[j - 1][i - 1] / BAsum1[i - 1] * BAsum[i - 1]

# 清理零值并格式化
for i in range(1, lastcol):
    for j in range(1, num3 + 1):
        if df_target.iat[j - 1, i] == 0:
            df_target.iat[j - 1, i] = ''
        else:
            df_target.iat[j - 1, i] = round(df_target.iat[j - 1, i], 2)

# 计算总计
for i in range(1, num3 + 1):
    total = sum([df_target.iat[i - 1, col] for col in [3, 4, 5, 14, 19]])
    df_target.iat[i - 1, 20] = round(total, 2)

# 计算部门总计
department_totals = [sum(df_target.iloc[:, col]) for col in [3, 4, 5, 14, 19, 20]]
for idx, val in enumerate(department_totals, start=1):
    df_target.iat[num3, idx + 3] = round(val, 2)

# 将更新后的数据写回到Excel
with pd.ExcelWriter(input_file_path, engine='openpyxl') as writer:
    writer.book = wb
    df_target.to_excel(writer, sheet_name='工时统计(财务费GP用)', index=False)

# 调整列宽
sheet = wb['工时统计(财务费GP用)']
for column in sheet.columns:
    max_length = 0
    column_letter = column[0].column_letter
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    sheet.column_dimensions[column_letter].width = adjusted_width

# 隐藏特定列
columns_to_hide = ['B', 'C', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'P', 'Q', 'R', 'S', 'V']
for col in columns_to_hide:
    sheet.column_dimensions[col].hidden = True

# 更改名称
sheet['D1'] = "制造部-设计部（HW）"
sheet['E1'] = "制造部-设计部（SW）"
sheet['F1'] = "制造部-工程部"
sheet['O1'] = "制造部-盘组部"
sheet['T1'] = "制造部-LF阀门生产"

wb.save(input_file_path)
print("处理完成")
#
if __name__ == "__main__":
    main()